package com.github.cakin.shiro.chapter16.dao;

import com.github.cakin.shiro.chapter16.entity.Organization;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @className: OrganizationDao
 * @description: 组织DAO
 * @date: 2020/5/25
 * @author: cakin
 */
@Repository
public class OrganizationDaoImpl implements OrganizationDao {
    /**
     * 注入jdbcTemplate
     */
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 功能描述：创建一个组织
     *
     * @param organization 组织
     * @return Organization 组织
     * @author cakin
     * @date 2020/5/25
     */
    public Organization createOrganization( final Organization organization ) {
        final String sql = "insert into sys_organization( name, parent_id, parent_ids, available) values(?,?,?,?)";

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement( Connection connection ) throws SQLException {
                PreparedStatement psst = connection.prepareStatement(sql, new String[]{"id"});
                int count = 1;
                psst.setString(count++, organization.getName());
                psst.setLong(count++, organization.getParentId());
                psst.setString(count++, organization.getParentIds());
                psst.setBoolean(count++, organization.getAvailable());
                return psst;
            }
        }, keyHolder);
        organization.setId(keyHolder.getKey().longValue());
        return organization;
    }

    /**
     * 功能描述：更新一个组织
     *
     * @param organization 组织
     * @return Organization 组织
     * @author cakin
     * @date 2020/5/25
     */
    public Organization updateOrganization( Organization organization ) {
        final String sql = "update sys_organization set name=?, parent_id=?, parent_ids=?, available=? where id=?";
        jdbcTemplate.update(
                sql,
                organization.getName(), organization.getParentId(), organization.getParentIds(), organization.getAvailable(), organization.getId());
        return organization;
    }

    /**
     * 功能描述：根据组织id删除该组织以及它的所有孩子
     *
     * @param organizationId 组织Id
     * @author cakin
     * @date 2020/5/25
     */
    public void deleteOrganization( Long organizationId ) {
        Organization organization = findOne(organizationId);
        // 删除自己
        final String deleteSelfSql = "delete from sys_organization where id=?";
        jdbcTemplate.update(deleteSelfSql, organizationId);
        // 删除自己所有的孩子
        final String deleteDescendantsSql = "delete from sys_organization where parent_ids like ?";
        jdbcTemplate.update(deleteDescendantsSql, organization.makeSelfAsParentIds() + "%");
    }

    /**
     * 功能描述：根据组织id查找一个组织
     *
     * @param organizationId 组织Id
     * @return Organization 组织
     * @author cakin
     * @date 2020/5/25
     */
    public Organization findOne( Long organizationId ) {
        final String sql = "select id, name, parent_id, parent_ids, available from sys_organization where id=?";
        List<Organization> organizationList = jdbcTemplate.query(sql, new BeanPropertyRowMapper(Organization.class), organizationId);
        if (organizationList.size() == 0) {
            return null;
        }
        return organizationList.get(0);
    }

    /**
     * 功能描述：列式所有组织
     *
     * @return List<Organization> 组织列表
     * @author cakin
     * @date 2020/5/25
     */
    public List<Organization> findAll() {
        final String sql = "select id, name, parent_id, parent_ids, available from sys_organization";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(Organization.class));
    }

    public List<Organization> findAllWithExclude( Organization excludeOraganization ) {
        //TODO 改成not exists 利用索引
        /**
         * 如果这里excludeOraganization是分公司1，也就是id为2的公司，查询语句如下
         * select id, name, parent_id, parent_ids, available from sys_organization where id!='2' and parent_ids not like '0/1/2/%'
         */
        final String sql = "select id, name, parent_id, parent_ids, available from sys_organization where id!=? and parent_ids not like ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(Organization.class), excludeOraganization.getId(),
                excludeOraganization.makeSelfAsParentIds() + "%");
    }

    /**
     * 功能描述：组织表更
     *
     * @param source 变更前组织
     * @param target 变更后的组织
     * @author cakin
     * @date 2020/5/25
     * @description:
     */
    public void move( Organization source, Organization target ) {
        // 修改组织结构，主要是修改父节点和父节点列表
        String moveSourceSql = "update sys_organization set parent_id=?,parent_ids=? where id=?";
        jdbcTemplate.update(moveSourceSql, target.getId(), target.getParentIds(), source.getId());
        /**
         * 修改孩子节点的组织结构
         * 1 先找到被修改节点的孩子节点，通过parent_ids like source.makeSelfAsParentIds() + "%" 条件可找到被修改节点的孩子节点
         * 2 找到孩子节点的父节点列表
         */
        String moveSourceDescendantsSql = "update sys_organization set parent_ids=concat(?, substring(parent_ids, length(?))) "
                + "where parent_ids like ?";
        jdbcTemplate.update(moveSourceDescendantsSql, target.makeSelfAsParentIds(), source.makeSelfAsParentIds(),
                source.makeSelfAsParentIds() + "%");
    }
}
